package com.icss.dao;


import com.icss.domian.QueryVO;
import com.icss.domian.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface IUserDao extends JpaRepository<User,Integer> , JpaSpecificationExecutor<User> {
    /**
     * JPQL：
     * 格式：from User where name=?index
     * select * from t_user where name=?
     * 表名对应实体类名字，字段名称对应属性名
     * ？index：表示占位符对应的参数的索引，从1开始
     * @Query(jpql语句)
     * @param
     * @return
     */

//查找用户列表
    @Modifying
    @Transactional
    @Query(value = " select t.user_id as userId , t.user_name as userName from t_user as t  ", nativeQuery = true)
    List<QueryVO> findAllUserName();
//根据ID查找电话号码和车牌号

    @Modifying
    @Transactional
    @Query(value = " select t.user_id as userId , t.user_name as userName from t_user as t  ", nativeQuery = true)
    List<QueryVO> findUser();
//更具ID查找车牌号

    @Modifying
    @Transactional
    @Query(value =" select r.illegal_record_id as illegalRecordId, r.illegal_record_license illegalRecordLicense ," +
            "u.phone  from  t_vehicle_illegal_record as  r ,t_user as  u " +
            "where  r.user_id=u.user_id and u.user_id=?1 " ,nativeQuery = true)
    List<QueryVO> findRecordLicenseByUserId(Integer UserId);

//    根据ID改手机号

    @Modifying
    @Transactional
    @Query(value = "update  t_user set phone=?2 where user_id=?1" ,nativeQuery = true)
    void updateUserPhoneByUserID(Integer UserId, String phone );

    @Query(value = "select t.user_id as userId, t.user_name as userName,t.phone phone,t.balance,t.illegal_count illegalCount," +
            "t.unsettled_illegal_count unsettledIllegalCount ,t.total_score total_score," +
            "t.unavailable_score unavailableScore ,t.available_score availableScore from t_user as t ", nativeQuery = true)
    List<QueryVO> findAllUserIllega();

    @Modifying
    @Transactional
    @Query(value = "update t_user set unsettled_illegal_count=unsettled_illegal_count-1 where user_id= ?1 " ,nativeQuery = true)
    void  updateUnsettledIllegalCount(Integer illegalRecordId);


    @Modifying
    @Transactional
    @Query(value = "update t_user set illegal_count=illegal_count+1 where user_id= ?1 ",nativeQuery = true)
    void updateIllegalCount(Integer userId);

    @Modifying
    @Transactional
    @Query(value = " update t_user set illegal_count=illegal_count+1," +
            " unavailable_score= unavailable_score + ?2 , " +
            "available_score=available_score- ?2 where user_id = ?1 " ,nativeQuery = true)
    void  updateIllegalCountAndSocre(Integer userId,Integer illegalItemScore);
}
